<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-us" lang="en-us">
<head>
  <link href="//gmpg.org/xfn/11" rel="profile">
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta name="generator" content="Hugo 0.68.3" />

  
  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  <title>mysql中in和exist &middot; 我的博客</title>

  
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/print.css" media="print">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/poole.css">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/syntax.css">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/hyde.css">
    <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Abril+Fatface|PT+Sans:400,400i,700">


  
  <link rel="apple-touch-icon-precomposed" sizes="144x144" href="/apple-touch-icon-144-precomposed.png">
  <link rel="shortcut icon" href="/favicon.png">

  
  
</head>

  <body class="theme-base-0b ">
  <aside class="sidebar">
  <div class="container sidebar-sticky">
    <div class="sidebar-about">
      <a href="/my_technology_blog/"><h1>我的博客</h1></a>
      <p class="lead">
       杨博的博客 
      </p>
    </div>

    <nav>
      <ul class="sidebar-nav">
        <li><a href="/my_technology_blog/">Home</a> </li>
        
      </ul>
    </nav>

    <p>&copy; 2021. All rights reserved. </p>
  </div>
</aside>

    <main class="content container">
    <div class="post">
  <h1>mysql中in和exist</h1>
  <time datetime=2020-08-07T21:02:56&#43;0800 class="post-date">Fri, Aug 7, 2020</time>
  <h3 id="exists和in">exists和in</h3>
<p><strong>in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。</strong>
如：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-csharp" data-lang="csharp">A<span style="color:#960050;background-color:#1e0010">：</span><span style="color:#66d9ef">select</span> * <span style="color:#66d9ef">from</span> t1 a <span style="color:#66d9ef">where</span> exists (<span style="color:#66d9ef">select</span> * <span style="color:#66d9ef">from</span> t2 b <span style="color:#66d9ef">where</span> b.id = a.id)
B<span style="color:#960050;background-color:#1e0010">：</span><span style="color:#66d9ef">select</span> * <span style="color:#66d9ef">from</span> t1 a <span style="color:#66d9ef">where</span> a.id <span style="color:#66d9ef">in</span> (<span style="color:#66d9ef">select</span> b.id <span style="color:#66d9ef">from</span> t2 b)
</code></pre></div><p>对于A，用到了t2上的id索引，exists执行次数为t1.length，不缓存exists的结果集。
对于B，用到了t1上的id索引，首先执行in语句，然后将结果缓存起来，之后遍历t1表，将满足结果的加入结果集，所以执行次数为t1.length*t2.length次。
<strong>因此对t1表大t2表小的情况使用in，t2表小t1表大的情况使用exists</strong></p>
<h3 id="not-exists和not-in">not exists和not in</h3>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-csharp" data-lang="csharp">A<span style="color:#960050;background-color:#1e0010">：</span><span style="color:#66d9ef">select</span> * <span style="color:#66d9ef">from</span> t1 a <span style="color:#66d9ef">where</span> not exists (<span style="color:#66d9ef">select</span> * <span style="color:#66d9ef">from</span> t2 b <span style="color:#66d9ef">where</span> b.id = a.id)
B<span style="color:#960050;background-color:#1e0010">：</span><span style="color:#66d9ef">select</span> * <span style="color:#66d9ef">from</span> t1 a <span style="color:#66d9ef">where</span> a.id not <span style="color:#66d9ef">in</span> (<span style="color:#66d9ef">select</span> b.id <span style="color:#66d9ef">from</span> t2 b)
</code></pre></div><p>对于A，和exists一样，用到了t2上的id索引，exists()执行次数为t1.length，不缓存exists()的结果集。
而对于B，因为not in实质上等于<code>!= and != ···</code>，因为!=不会使用索引，故not in不会使用索引。
因此，<strong>不管t1和t2大小如何，均使用not exists效率会更高。</strong></p>

</div>


    </main>

    
  </body>
</html>
